package com.zw.mes.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/**
 * @Description: Excel导入导出工具类，使用阿里 easyExcel 类库
 *
 * @Author: China.sgl
 * @Date: 2020/7/30 9:42
 */
@SuppressWarnings("all")
public class ExcelUtils {

    /**
     * excel 导出
     *
     * @param fileName  文件名
     * @param cls       导出模型类
     * @param sheetName sheet名
     * @param list      数据
     * @param response  response
     */
    public static void export(String fileName, Class cls, String sheetName, List list, HttpServletResponse response) {
        genExcel(fileName, cls, sheetName, list, null);
        FileUtils.downloadFile(fileName, response);
    }

    /**
     * excel 导出
     *
     * @param fileName  文件名
     * @param cls       导出模型类
     * @param sheetName sheet名
     * @param list      数据
     * @param comments  表头批注
     * @param response  response
     */
    public static void export(String fileName, Class cls, String sheetName, List list, List<String> comments, HttpServletResponse response) {
        genExcel(fileName, cls, sheetName, list, comments);
        FileUtils.downloadFile(fileName, response);
    }

    /**
     * excel 导出
     *
     * @param path      路径
     * @param fileName  文件名
     * @param cls       导出模型类
     * @param sheetName sheet名
     * @param list      数据
     * @param response  response
     */
    public static void export(String path, String fileName, Class cls, String sheetName, List list,  HttpServletResponse response) {
        FileUtils.mkdirs(path);
        export(path + fileName, cls, sheetName, list, null, response);
    }

    /**
     * excel 导出
     *
     * @param path      路径
     * @param fileName  文件名
     * @param cls       导出模型类
     * @param sheetName sheet名
     * @param list      数据
     * @param comments  表头批注
     * @param response  response
     */
    public static void export(String path, String fileName, Class cls, String sheetName, List list, List<String> comments, HttpServletResponse response) {
        FileUtils.mkdirs(path);
        export(path + fileName, cls, sheetName, list, comments, response);
    }

    /**
     * 生成excel
     *
     * @param path      路径
     * @param fileName  文件名
     * @param cls       导出模型类
     * @param sheetName sheet名
     * @param list      数据
     * @param comments  表头批注
     */
    public static void genExcel(String path, String fileName, Class cls, String sheetName, List list, List<String> comments) {
        FileUtils.mkdirs(path);
        genExcel(path + fileName, cls, sheetName, list, comments);
    }

    /**
     * 生成excel
     *
     * @param fileName  文件名
     * @param cls       导出模型类
     * @param sheetName sheet名
     * @param list      数据
     * @param comments  表头批注
     */
    public static void genExcel(String fileName, Class cls, String sheetName, List list, List<String> comments) {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)10);
        headWriteCellStyle.setWriteFont(headWriteFont);

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short)10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(fileName, cls).registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(new CommentWriteHandler(comments))
                .sheet(sheetName).doWrite(list);
    }

    /**
     * 导入文件
     *
     * @param file     文件
     * @param cls      实体类型
     * @param listener 解析器
     * @throws IOException IO异常
     */
    public static void importExcel(MultipartFile file, Class cls, AnalysisEventListener listener) throws IOException {
        EasyExcel.read(file.getInputStream(), cls, listener).headRowNumber(1).sheet().doRead();
    }

    static class CommentWriteHandler implements RowWriteHandler {

        private List<String> comments;

        public CommentWriteHandler(List<String> comments) {
            this.comments = comments;
        }

        @Override
        public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {

        }

        @Override
        public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {

        }

        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
            if (isHead && comments != null) {
                Sheet sheet = writeSheetHolder.getSheet();
                Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
                // 在第一行 第二列创建一个批注

                for (int i = 0; i < comments.size(); i++) {
                    Comment comment = drawingPatriarch.createCellComment(new HSSFClientAnchor(0, 0, 0, 0, (short)i, 0, (short)(i+1), 1));
                    // 输入批注信息
                    comment.setString(new HSSFRichTextString(comments.get(i)));
                    // 将批注添加到单元格对象中
                    sheet.getRow(0).getCell(i).setCellComment(comment);
                }

            }
        }
    }

}
